package model.datasource;

public class SQLStatements
{
	public static final String CREATE_STUDENT_TABLE = "CREATE TABLE IF NOT EXISTS 'student'" +
			"( " +
			"student_id INTEGER PRIMARY KEY AUTOINCREMENT," +
			"student_name TEXT," +
			"student_lastname TEXT," +
			"student_nickname TEXT," +
			"student_school TEXT," +
			"student_phonenumber TEXT," +
			"student_email TEXT," +
			"student_ageyear INTEGER" +
			" )";
	
	public static final String CREATE_LESSON_TABLE = "CREATE TABLE IF NOT EXISTS 'lecture' " +
			"( " +
			"lecture_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
			"lecture_student_id INTEGER," +
			"lecture_title TEXT," +
			"lecture_content TEXT," +
			"lecture_income INTEGER," +
			"lecture_date TEXT, " +
			"FOREIGN KEY(lecture_student_id) REFERENCES student(student_id) ON DELETE CASCADE" +
			")";
	
	//================================================student===============================================//
	public static final String INSERT_NEW_STUDENT = "INSERT INTO STUDENT " +
			"(student_name, student_lastname, student_nickname, student_school, student_phonenumber, student_email, student_ageyear) VALUES " +
			"(?, ?, ?, ?, ?, ?, ?) ";
	
	public static final String REQUEST_STUDENTS = "SELECT * FROM student";
	public static final String UPDATE_STUDENT = "UPDATE student SET student_name=?, student_lastname=?, student_nickname=?, student_school=?, student_phonenumber=?, " +
			"student_email=?, student_ageyear=? WHERE student_id=?";
	public static final String DELETE_STUDENT = "DELETE FROM student WHERE student_id = ?";
	public static final String DELETE_STUDENT_LESSONS = "DELETE FROM lecture WHERE lecture_student_id=?";
	//=================================================lecture===============================================//
	public static final String INSERT_NEW_LESSON = "INSERT INTO lecture " +
			"( lecture_student_id, lecture_title, lecture_content, lecture_date, lecture_income ) VALUES " +
			"(?, ?, ?, ?, ?)";
	public static final String REQUEST_LESSONS = "SELECT * FROM lecture";
	public static final String UPDATE_LESSON = "UPDATE lecture SET lecture_student_id=?, lecture_title=? , lecture_content=? , lecture_income=? , lecture_date=? WHERE lecture_id=?";
	public static final String DELETE_LESSON = "DELETE FROM lecture WHERE lecture_id = ?";
}
